package vip.xiaonuo.modular.databaseinfo.service.impl;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import vip.xiaonuo.core.pojo.page.PageResult;
import vip.xiaonuo.modular.databaseinfo.entity.DatabaseInfo;
import vip.xiaonuo.modular.databaseinfo.param.DatabaseInfoParam;
import vip.xiaonuo.modular.tableinfo.entity.TableInfo;

import java.sql.*;
import java.sql.Date;
import java.text.MessageFormat;
import java.util.*;
import java.util.function.Function;

public class OracleTableInfoUtil extends AbstractDBTableInfoUtil {

    private static final String OWNER = "OWNER";
    private static final String TABLE_NAME = "TABLE_NAME";
    private static final String COMMENTS = "COMMENTS";
    private static final String CREATED = "CREATED";
    private static final String LAST_DDL_TIME = "LAST_DDL_TIME";

    private static final String EXCLUDE_SCHEMA_PREFIX_APEX = "'APEX%'";
    private static final String EXCLUDE_TABLESPACE_PREFIX_SYSTEM = "'SYS%'";


    public OracleTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);

        // Oracle的系统 schema 非常多，无法列全，此处是根据我自己的oracle中已经存在的 系统schema 进行列举的
        excludeSchema = "" +
                "('ANONYMOUS', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', " +
                "'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', " +
                "'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OJVMSYS', " +
                "'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', " +
                "'OUTLN', 'OWBSYS', 'OWBSYS_AUDIT', 'SCOTT', 'SI_INFORMTN_SCHEMA', " +
                "'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYS', 'SYSBACKUP', " +
                "'SYSDG', 'SYSKM', 'SYSMAN', 'SYSTEM', 'WMSYS', 'XDB', 'XS$NULL')";
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.ORACLE;
    }

    @Override
    protected String getTableCountSQL() {
        // 大家可以注意一下，这里我还加了两个限制条件
        // 1. OWNER NOT LIKE 'APEX%'
        // 2. TABLESPACE_NAME NOT LIKE 'SYS%'
        // 这两个条件都是为了过滤 系统schema 的
        String sql = MessageFormat.format("SELECT COUNT(1) FROM ALL_TABLES WHERE OWNER NOT IN {0} AND OWNER NOT LIKE {1} AND TABLESPACE_NAME NOT LIKE {2}",
                excludeSchema, EXCLUDE_SCHEMA_PREFIX_APEX, EXCLUDE_TABLESPACE_PREFIX_SYSTEM);
        System.out.println("sql");
        return sql;
    }

    @Override
    protected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {
        List tableProperties = new ArrayList();

        final int start = (pageNumber - 1) * pageSize + 1;
        final int end = start + pageSize;

        // oracle 的相关信息需要从几张表中关联得出
        // 1. ALL_TABLES
        // 2. ALL_OBJECTS
        // 3. USER_TAB_COMMENTS
        // 另外，请注意一下 Oracle 的分页方式。其中，为了让结果按 TABLE_NAME排序，我们必须做一个子查询，否则，分页出来的结果可能不是你想要的
        String sql = MessageFormat.format(
                "" +
                        "SELECT " +
                        "  * " +
                        "FROM " +
                        "  (" +
                        "    SELECT " +
                        "      AT.*, UTC.{2} {2}, AO.{3} {3}, AO.{4} {4}, ROWNUM RN" +
                        "    FROM" +
                        "      (" +
                        "        SELECT" +
                        "          {0}, {1}" +
                        "        FROM" +
                        "          ALL_TABLES" +
                        "        WHERE" +
                        "          OWNER NOT IN {5}" +
                        "          AND" +
                        "          OWNER NOT LIKE {6}" +
                        "          AND" +
                        "          TABLESPACE_NAME NOT LIKE {7}" +
                        "        ORDER BY {1}" +
                        "      ) AT" +

                        "      LEFT JOIN" +
                        "      ALL_OBJECTS AO" +
                        "      ON" +
                        "        AT.OWNER=AO.OWNER" +
                        "        AND" +
                        "        AT.TABLE_NAME=AO.OBJECT_NAME" +

                        "      LEFT JOIN" +
                        "      USER_TAB_COMMENTS UTC" +
                        "      ON" +
                        "        AO.OBJECT_NAME=UTC.TABLE_NAME" +

                        "    WHERE" +
                        "      ROWNUM<?" +
                        "  ) " +
                        "WHERE" +
                        "  RN>=?",
                OWNER, TABLE_NAME, COMMENTS, CREATED, LAST_DDL_TIME, excludeSchema, EXCLUDE_SCHEMA_PREFIX_APEX, EXCLUDE_TABLESPACE_PREFIX_SYSTEM);
        System.out.println("sql2"+sql);
        System.out.println(dataSource.url);
        System.out.println(dataSource.username);
        System.out.println(dataSource.password);
        Properties conProps = new Properties();
        conProps.put("user", "sys");
        conProps.put("password", "root");
        conProps.put("defaultRowPrefetch", "15");
        conProps.put("internal_logon", "sysdba");
        try (Connection connection = DriverManager.getConnection(dataSource.url, conProps);
             PreparedStatement ps = connection.prepareStatement(sql)) {
            System.out.println("333333333333333333333333333333333"+sql);
            ps.setInt(1, end);
            ps.setInt(2, start);

            ResultSet resultSet = ps.executeQuery();
            System.out.println("222222222222222222222222"+resultSet);
            while (resultSet.next()) {
//                TableInfo tableInfo = new TableInfo();
//
//                tableInfo.schema = resultSet.getString(OWNER);
//                tableInfo.tableName = resultSet.getString(TABLE_NAME);
//                tableInfo.tableComment = resultSet.getString(COMMENTS);
//
//                Date createTime = resultSet.getDate(CREATED);
//                tableInfo.createTime = createTime != null ? createTime.getTime() : -1;
//
//                Date updateTime = resultSet.getDate(LAST_DDL_TIME);
//                tableInfo.updateTime = updateTime != null ? updateTime.getTime() : tableInfo.createTime;
//
//                tableProperties.add(tableInfo.toMap());
            }
        } catch (Exception e) {
            System.out.println(e.toString());
            System.err.println("error");
        }

        return tableProperties;
    }

    @Override
    public PageResult<DatabaseInfo> page(DatabaseInfoParam databaseInfoParam) {
        return null;
    }

    @Override
    public List<DatabaseInfo> list(DatabaseInfoParam databaseInfoParam) {
        return null;
    }

    @Override
    public void add(DatabaseInfoParam databaseInfoParam) {

    }

    @Override
    public void delete(List<DatabaseInfoParam> databaseInfoParamList) {

    }

    @Override
    public void edit(DatabaseInfoParam databaseInfoParam) {

    }

    @Override
    public DatabaseInfo detail(DatabaseInfoParam databaseInfoParam) {
        return null;
    }

    @Override
    public void export(DatabaseInfoParam databaseInfoParam) {

    }

    @Override
    public List<TableInfo> queryNum(String tio) {
        return null;
    }

    @Override
    public boolean saveBatch(Collection<DatabaseInfo> entityList, int batchSize) {
        return false;
    }

    @Override
    public boolean saveOrUpdateBatch(Collection<DatabaseInfo> entityList, int batchSize) {
        return false;
    }

    @Override
    public boolean updateBatchById(Collection<DatabaseInfo> entityList, int batchSize) {
        return false;
    }

    @Override
    public boolean saveOrUpdate(DatabaseInfo entity) {
        return false;
    }

    @Override
    public DatabaseInfo getOne(Wrapper<DatabaseInfo> queryWrapper, boolean throwEx) {
        return null;
    }

    @Override
    public Map<String, Object> getMap(Wrapper<DatabaseInfo> queryWrapper) {
        return null;
    }

    @Override
    public <V> V getObj(Wrapper<DatabaseInfo> queryWrapper, Function<? super Object, V> mapper) {
        return null;
    }

    @Override
    public BaseMapper<DatabaseInfo> getBaseMapper() {
        return null;
    }

    @Override
    public Class<DatabaseInfo> getEntityClass() {
        return null;
    }
}
